﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using Telerik.Web.UI;

namespace LenderClearingHouse
{
    public partial class NetSheetPrint : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataAccess da = new DataAccess();
                DataTable dt = da.GetDataTable("getNetSheet", new string[] { "@PropertyId" }, new object[] { GetPropertyId() });

                tbSellingPrice.InnerText = Convert.ToDouble(dt.Rows[0]["BuyerPurchasePrice"]).ToString("c");
                tbRehabCostEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["RehabCosts"]).ToString("c");
                tbPurchasePriceEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["PurchasePrice"]).ToString("c");

                tbLockEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_LockChange_Estimated"]).ToString("c");
                tbLockActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_LockChange_Actual"]).ToString("c");
                tbLockLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_LockChange_WithLoan"]).ToString("c");

                tbReserveEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ReserveForUtilites_Estimated"]).ToString("c");
                tbReserveActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ReserveForUtilites_Actual"]).ToString("c");
                tbReserveLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ReserveForUtilites_WithLoan"]).ToString("c");

                tbInsuranceEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Insurance_Estimated"]).ToString("c");
                tbInsuranceActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Insurance_Actual"]).ToString("c");
                tbInsuranceLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Insurance_WithLoan"]).ToString("c");
 
                tbCashEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_CashForKeys_Estimated"]).ToString("c");
                tbCashActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_CashForKeys_Actual"]).ToString("c");
                tbCashLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_CashForKeys_WithLoan"]).ToString("c");

                tbMiscEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Miscellaenous_Estimated"]).ToString("c");
                tbMiscActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Miscellaenous_Actual"]).ToString("c");
                tbMiscLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Miscellaenous_WithLoan"]).ToString("c");

                tbBrokerPercent.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_BrokerCoop"]).ToString("#0.00") + "%";
                tbSellerPercent.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_SellerClosingIncentives"]).ToString("#0.00") + "%";
                tbEscrowPercent.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_EscrowClosingEstimate"]).ToString("#0.00") + "%";
                
                tbSellersCostEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_SellersCostRepairs_Estimated"]).ToString("c");
                tbSellersCostActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_SellersCostRepairs_Actual"]).ToString("c");
                tbSellersCostLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_SellersCostRepairs_WithLoan"]).ToString("c");

                tbHomeEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_HomeWarranty_Estimated"]).ToString("c");
                tbHomeActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_HomeWarranty_Actual"]).ToString("c");
                tbHomeLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_HomeWarranty_WithLoan"]).ToString("c");

                tbAppraisalEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Appraisal_Estimated"]).ToString("c");
                tbAppraisalActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Appraisal_Actual"]).ToString("c");
                tbAppraisalLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Appraisal_WithLoan"]).ToString("c");
                
                tbMiscClosingEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_MiscellaneousClosingCosts_Estimated"]).ToString("c");
                tbMiscClosingActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_MiscellaneousClosingCosts_Actual"]).ToString("c");
                tbMiscClosingLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_MiscellaneousClosingCosts_WithLoan"]).ToString("c");
                
                tbHoaEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_HOADues_Estimated"]).ToString("c");
                tbHoaActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_HOADues_Actual"]).ToString("c");
                tbHoaLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_HOADues_WithLoan"]).ToString("c");

                tbTransactionEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_TransactionFee_Estimated"]).ToString("c");
                tbTransactionActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_TransactionFee_Actual"]).ToString("c");
                tbTransactionLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_TransactionFee_WithLoan"]).ToString("c");

                tbOtherEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Other_Estimated"]).ToString("c");
                tbOtherActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Other_Actual"]).ToString("c");
                tbOtherLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Other_WithLoan"]).ToString("c");
                tbProfitParticipationPercent.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_FeeLCH"]).ToString("#0.00") + "%";

                tbNonProfitFeePercent.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_NonProfitFee"]).ToString("#0.00") + "%";
                tbClosingCostsPercent.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ClosingCosts"]).ToString("#0.00") + "%"; 
                tbLoanBrokerFee.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanBrokerFee"]).ToString("c");
                tbLoanFeePercent.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanFee"]).ToString("#0.00") + "%";
                tbLoanInterestPercent.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanInterest"]).ToString("#0.00") + "%";

                tbRehabCostActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_RehabCost_Actual"]).ToString("c");
                tbRehabCostLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_RehabCost_WithLoan"]).ToString("c");

                tb1stDOT.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_1stDOT"]).ToString("c");
                tbDownPayment.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_DownPayment"]).ToString("c");
                                
                cbMortgageType.InnerText = dt.Rows[0]["NetSheet_MortgageType"].ToString();
                tbEstClosingDate.InnerText = dt.Rows[0].IsNull("NetSheet_EstClosingDate") ? "" : dt.Rows[0]["NetSheet_EstClosingDate"].ToString();

                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ClosingCosts_Estimated"]))
                    tbClosingCostsEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ClosingCosts_Estimated"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ClosingCosts_Actual"]))
                    tbClosingCostsActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ClosingCosts_Actual"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ClosingCosts_WithLoan"]))
                    tbClosingCostsLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ClosingCosts_WithLoan"]).ToString("c");
                
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_NonProfitFee_Estimated"]))
                    tbNonProfitFeeEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_NonProfitFee_Estimated"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_NonProfitFee_Actual"]))
                    tbNonProfitFeeActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_NonProfitFee_Actual"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_NonProfitFee_WithLoan"]))
                    tbNonProfitFeeLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_NonProfitFee_WithLoan"]).ToString("c");

                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Broker_Estimated"]))
                    tbBrokerEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Broker_Estimated"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Broker_Actual"]))
                    tbBrokerActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Broker_Actual"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Broker_WithLoan"]))
                    tbBrokerLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Broker_WithLoan"]).ToString("c");

                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_SellerClosing_Estimated"]))
                    tbSellerClosingEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_SellerClosing_Estimated"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_SellerClosing_Actual"]))
                    tbSellerClosingActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_SellerClosing_Actual"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_SellerClosing_WithLoan"]))
                    tbSellerClosingLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_SellerClosing_WithLoan"]).ToString("c");

                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Escrow_Estimated"]))
                    tbEscrowEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Escrow_Estimated"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Escrow_Actual"]))
                    tbEscrowActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Escrow_Actual"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Escrow_WithLoan"]))
                    tbEscrowLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_Escrow_WithLoan"]).ToString("c");

                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ProfitParticipation_Estimated"]))
                    tbProfitParticipationEstimated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ProfitParticipation_Estimated"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ProfitParticipation_Actual"]))
                    tbProfitParticipationActual.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ProfitParticipation_Actual"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ProfitParticipation_WithLoan"]))
                    tbProfitParticipationLoan.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_ProfitParticipation_WithLoan"]).ToString("c");

                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_LoanFee"]))
                    tbLoanFeeCalculated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanFeeSumma"]).ToString("c");
                if (Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_LoanInterest"]))
                    tbLoanInterestCalculated.InnerText = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanInterestSumma"]).ToString("c");

                if (!dt.Rows[0].IsNull("NetSheet_Occupancy"))
                    if ((dt.Rows[0]["NetSheet_Occupancy"].ToString().ToLower() == "vacant") || (dt.Rows[0]["NetSheet_Occupancy"].ToString().ToLower() == "occupied"))
                        ddlOccupancy.InnerText = dt.Rows[0]["NetSheet_Occupancy"].ToString();

                if (!dt.Rows[0].IsNull("NetSheet_AcquisitionDate"))
                    tbAcquisitionDate.InnerText = Convert.ToDateTime(dt.Rows[0]["NetSheet_AcquisitionDate"]).ToString("MM/dd/yyyy");
                                
                RefreshLoan(IsLoan());
                Calculate(dt.Rows[0]);

                edPropertyLocation.InnerText = Convert.ToString(Session["PropertyLocation"]);
                edInvestorName.InnerText = Convert.ToString(Session["InvestorName"]);
                edBedrooms.InnerText = Convert.ToString(Session["Bedrooms"]) + "/"
                    + Convert.ToString(Session["Bathrooms"]);
                edConstructYear.InnerText = Convert.ToString(Session["ConstructYear"]);
                edGarageSqft.InnerText = Convert.ToString(Session["GarageSqft"]);
                edPool.InnerText = Convert.ToString(Session["Pool"]);
                edTotalSqft.InnerText = Convert.ToString(Session["TotalSqft"]);

                ClientScript.RegisterStartupScript(this.GetType(), "Print", "window.print();", true);
            }            
        }

        protected void Page_PreRender(object sender, EventArgs e)
        {
          
        }

        private void Calculate(DataRow dr)
        {
            #region AcquisitionCost
            
            double dblPurchasePriceEstimated = Convert.ToDouble(dr["PurchasePrice"]);
            double dblPurchasePriceActual = Convert.ToDouble(dr["NetSheet_PurchasePrice_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_PurchasePrice_Actual"]))
                dblPurchasePriceActual = dblPurchasePriceEstimated;
            tbPurchasePriceActual.InnerText = dblPurchasePriceActual.ToString("c");
            double dblPurchasePriceLoan = Convert.ToDouble(dr["NetSheet_PurchasePrice_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_PurchasePrice_WithLoan"]))
                dblPurchasePriceLoan = dblPurchasePriceActual;
            tbPurchasePriceLoan.InnerText = dblPurchasePriceLoan.ToString("c");

            double dblNonProfitFeePercent = Convert.ToDouble(dr["NetSheet_NonProfitFee"]);
            double dblNonProfitFeeEstimated = Convert.ToDouble(dr["NetSheet_NonProfitFee_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NonProfitFee_Estimated"]))
                dblNonProfitFeeEstimated = dblPurchasePriceEstimated * dblNonProfitFeePercent / 100;
            tbNonProfitFeeEstimated.InnerText = dblNonProfitFeeEstimated.ToString("c");
            double dblNonProfitFeeActual = Convert.ToDouble(dr["NetSheet_NonProfitFee_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NonProfitFee_Actual"]))
                dblNonProfitFeeActual = dblPurchasePriceActual * dblNonProfitFeePercent / 100;
            tbNonProfitFeeActual.InnerText = dblNonProfitFeeActual.ToString("c");
            double dblNonProfitFeeLoan = Convert.ToDouble(dr["NetSheet_NonProfitFee_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NonProfitFee_WithLoan"]))
                dblNonProfitFeeLoan = dblPurchasePriceLoan * dblNonProfitFeePercent / 100;
            tbNonProfitFeeLoan.InnerText = dblNonProfitFeeLoan.ToString("c");
            
            double dblClosingCostsPercent = Convert.ToDouble(dr["NetSheet_ClosingCosts"]);
            double dblClosingCostsEstimated = Convert.ToDouble(dr["NetSheet_ClosingCosts_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ClosingCosts_Estimated"]))
                dblClosingCostsEstimated = dblPurchasePriceEstimated * dblClosingCostsPercent / 100;
            tbClosingCostsEstimated.InnerText = dblClosingCostsEstimated.ToString("c");
            double dblClosingCostsActual = Convert.ToDouble(dr["NetSheet_ClosingCosts_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ClosingCosts_Actual"]))
                dblClosingCostsActual = dblPurchasePriceActual * dblClosingCostsPercent / 100;
            tbClosingCostsActual.InnerText = dblClosingCostsActual.ToString("c");
            double dblClosingCostsLoan = Convert.ToDouble(dr["NetSheet_ClosingCosts_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ClosingCosts_WithLoan"]))
                dblClosingCostsLoan = dblPurchasePriceLoan * dblClosingCostsPercent / 100;
            tbClosingCostsLoan.InnerText = dblClosingCostsLoan.ToString("c");
            
            double dblAcquisitionEstimated = Convert.ToDouble(dr["NetSheet_Total_AcquisitionCost_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_AcquisitionCost_Estimated"]))
                dblAcquisitionEstimated = dblPurchasePriceEstimated + dblNonProfitFeeEstimated + dblClosingCostsEstimated;
            tbAcquisitionEstimated.InnerText = dblAcquisitionEstimated.ToString("c");
            double dblAcquisitionActual = Convert.ToDouble(dr["NetSheet_Total_AcquisitionCost_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_AcquisitionCost_Actual"]))
                dblAcquisitionActual = dblPurchasePriceActual + dblNonProfitFeeActual + dblClosingCostsActual;
            tbAcquisitionActual.InnerText = dblAcquisitionActual.ToString("c");
            double dblAcquisitionLoan = Convert.ToDouble(dr["NetSheet_Total_AcquisitionCost_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_AcquisitionCost_WithLoan"]))
                dblAcquisitionLoan = dblPurchasePriceLoan + dblNonProfitFeeLoan + dblClosingCostsLoan;
            tbAcquisitionLoan.InnerText = dblAcquisitionLoan.ToString("c");
           
            #endregion

            #region PreparationCost

            double dblRehabCostEstimated = Convert.ToDouble(dr["RehabCosts"]);
            double dblRehabCostActual = Convert.ToDouble(dr["NetSheet_RehabCost_Actual"]);
            double dblRehabCostLoan = Convert.ToDouble(dr["NetSheet_RehabCost_WithLoan"]);
            
            double dblLockEstimated = Convert.ToDouble(dr["NetSheet_LockChange_Estimated"]);
            double dblLockActual = Convert.ToDouble(dr["NetSheet_LockChange_Actual"]);
            double dblLockLoan = Convert.ToDouble(dr["NetSheet_LockChange_WithLoan"]);
                        
            double dblReserveEstimated = Convert.ToDouble(dr["NetSheet_ReserveForUtilites_Estimated"]);
            double dblReserveActual = Convert.ToDouble(dr["NetSheet_ReserveForUtilites_Actual"]);
            double dblReserveLoan = Convert.ToDouble(dr["NetSheet_ReserveForUtilites_WithLoan"]);
            
            double dblInsuranceEstimated = Convert.ToDouble(dr["NetSheet_Insurance_Estimated"]);
            double dblInsuranceActual = Convert.ToDouble(dr["NetSheet_Insurance_Actual"]);
            double dblInsuranceLoan = Convert.ToDouble(dr["NetSheet_Insurance_WithLoan"]);
            
            double dblCashEstimated = Convert.ToDouble(dr["NetSheet_CashForKeys_Estimated"]);
            double dblCashActual = Convert.ToDouble(dr["NetSheet_CashForKeys_Actual"]);
            double dblCashLoan = Convert.ToDouble(dr["NetSheet_CashForKeys_WithLoan"]);
            
            double dblMiscEstimated = Convert.ToDouble(dr["NetSheet_Miscellaenous_Estimated"]);
            double dblMiscActual = Convert.ToDouble(dr["NetSheet_Miscellaenous_Actual"]);
            double dblMiscLoan = Convert.ToDouble(dr["NetSheet_Miscellaenous_WithLoan"]);

            double dblPreparationEstimated = Convert.ToDouble(dr["NetSheet_Total_PreparationCost_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PreparationCost_Estimated"]))
                dblPreparationEstimated = dblRehabCostEstimated + dblLockEstimated + dblReserveEstimated + dblInsuranceEstimated + dblCashEstimated + dblMiscEstimated;
            tbPreparationEstimated.InnerText = dblPreparationEstimated.ToString("c");
            double dblPreparationActual = Convert.ToDouble(dr["NetSheet_Total_PreparationCost_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PreparationCost_Actual"]))
                dblPreparationActual = dblRehabCostActual + dblLockActual + dblReserveActual + dblInsuranceActual + dblCashActual + dblMiscActual;
            tbPreparationActual.InnerText = dblPreparationActual.ToString("c");
            double dblPreparationLoan = Convert.ToDouble(dr["NetSheet_Total_PreparationCost_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PreparationCost_WithLoan"]))
                dblPreparationLoan = dblRehabCostLoan + dblLockLoan + dblReserveLoan + dblInsuranceLoan + dblCashLoan + dblMiscLoan;
            tbPreparationLoan.InnerText = dblPreparationLoan.ToString("c");
            
            double dblTotalPresaleEstimated = Convert.ToDouble(dr["NetSheet_Total_PresaleCost_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PresaleCost_Estimated"]))
                dblTotalPresaleEstimated = dblAcquisitionEstimated + dblPreparationEstimated;
            tbTotalPresaleEstimated.InnerText = dblTotalPresaleEstimated.ToString("c");
            double dblTotalPresaleActual = Convert.ToDouble(dr["NetSheet_Total_PresaleCost_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PresaleCost_Actual"]))
                dblTotalPresaleActual = dblAcquisitionActual + dblPreparationActual;
            tbTotalPresaleActual.InnerText = dblTotalPresaleActual.ToString("c");
            double dblTotalPresaleLoan = Convert.ToDouble(dr["NetSheet_Total_PresaleCost_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PresaleCost_WithLoan"]))
                dblTotalPresaleLoan = dblAcquisitionLoan + dblPreparationLoan;
            tbTotalPresaleLoan.InnerText = dblTotalPresaleLoan.ToString("c");

            #endregion

            #region ClosingCost

            double dblSellingPrice = Convert.ToDouble(dr["BuyerPurchasePrice"]);
            
            double dblSellingPriceEstimated = Convert.ToDouble(dr["NetSheet_SellingPrice_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellingPrice_Estimated"]))
                dblSellingPriceEstimated = dblSellingPrice;
            tbSellingPriceEstimated.InnerText = dblSellingPriceEstimated.ToString("c");
            double dblSellingPriceActual = Convert.ToDouble(dr["NetSheet_SellingPrice_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellingPrice_Actual"]))
                dblSellingPriceActual = dblSellingPrice;
            tbSellingPriceActual.InnerText = dblSellingPriceActual.ToString("c");
            double dblSellingPriceLoan = Convert.ToDouble(dr["NetSheet_SellingPrice_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellingPrice_WithLoan"]))
                dblSellingPriceLoan = dblSellingPrice;
            tbSellingPriceLoan.InnerText = dblSellingPriceLoan.ToString("c");

            double dblBrokerPercent = Convert.ToDouble(dr["NetSheet_BrokerCoop"]);
            double dblSellerPercent = Convert.ToDouble(dr["NetSheet_SellerClosingIncentives"]);
            double dblEscrowPercent = Convert.ToDouble(dr["NetSheet_EscrowClosingEstimate"]);

            double dblBrokerEstimated = Convert.ToDouble(dr["NetSheet_Broker_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Broker_Estimated"]))
                dblBrokerEstimated = dblSellingPriceEstimated * dblBrokerPercent / 100;
            tbBrokerEstimated.InnerText = dblBrokerEstimated.ToString("c");
            double dblBrokerActual = Convert.ToDouble(dr["NetSheet_Broker_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Broker_Actual"]))
                dblBrokerActual = dblSellingPriceActual * dblBrokerPercent / 100;
            tbBrokerActual.InnerText = dblBrokerActual.ToString("c");
            double dblBrokerLoan = Convert.ToDouble(dr["NetSheet_Broker_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Broker_WithLoan"]))
                dblBrokerLoan = dblSellingPriceLoan * dblBrokerPercent / 100;
            tbBrokerLoan.InnerText = dblBrokerLoan.ToString("c");
            
            double dblSellerClosingEstimated = Convert.ToDouble(dr["NetSheet_SellerClosing_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellerClosing_Estimated"]))
                dblSellerClosingEstimated = dblSellingPriceEstimated * dblSellerPercent / 100;
            tbSellerClosingEstimated.InnerText = dblSellerClosingEstimated.ToString("c");
            double dblSellerClosingActual = Convert.ToDouble(dr["NetSheet_SellerClosing_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellerClosing_Actual"]))
                dblSellerClosingActual = dblSellingPriceActual * dblSellerPercent / 100;
            tbSellerClosingActual.InnerText = dblSellerClosingActual.ToString("c");
            double dblSellerClosingLoan = Convert.ToDouble(dr["NetSheet_SellerClosing_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellerClosing_WithLoan"]))
                dblSellerClosingLoan = dblSellingPriceLoan * dblSellerPercent / 100;
            tbSellerClosingLoan.InnerText = dblSellerClosingLoan.ToString("c");
            
            double dblEscrowEstimated = Convert.ToDouble(dr["NetSheet_Escrow_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Escrow_Estimated"]))
                dblEscrowEstimated = dblSellingPriceEstimated * dblEscrowPercent / 100;
            tbEscrowEstimated.InnerText = dblEscrowEstimated.ToString("c");
            double dblEscrowActual = Convert.ToDouble(dr["NetSheet_Escrow_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Escrow_Actual"]))
                dblEscrowActual = dblSellingPriceActual * dblEscrowPercent / 100;
            tbEscrowActual.InnerText = dblEscrowActual.ToString("c");
            double dblEscrowLoan = Convert.ToDouble(dr["NetSheet_Escrow_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Escrow_WithLoan"]))
                dblEscrowLoan = dblSellingPriceLoan * dblEscrowPercent / 100;
            tbEscrowLoan.InnerText = dblEscrowLoan.ToString("c");

            double dblSellersCostEstimated = Convert.ToDouble(dr["NetSheet_SellersCostRepairs_Estimated"]);
            double dblSellersCostActual = Convert.ToDouble(dr["NetSheet_SellersCostRepairs_Actual"]);
            double dblSellersCostLoan = Convert.ToDouble(dr["NetSheet_SellersCostRepairs_WithLoan"]);

            double dblHomeEstimated = Convert.ToDouble(dr["NetSheet_HomeWarranty_Estimated"]);
            double dblHomeActual = Convert.ToDouble(dr["NetSheet_HomeWarranty_Actual"]);
            double dblHomeLoan = Convert.ToDouble(dr["NetSheet_HomeWarranty_WithLoan"]);

            double dblAppraisalEstimated = Convert.ToDouble(dr["NetSheet_Appraisal_Estimated"]);
            double dblAppraisalActual = Convert.ToDouble(dr["NetSheet_Appraisal_Actual"]);
            double dblAppraisalLoan = Convert.ToDouble(dr["NetSheet_Appraisal_WithLoan"]);

            double dblMiscClosingEstimated = Convert.ToDouble(dr["NetSheet_MiscellaneousClosingCosts_Estimated"]);
            double dblMiscClosingActual = Convert.ToDouble(dr["NetSheet_MiscellaneousClosingCosts_Actual"]);
            double dblMiscClosingLoan = Convert.ToDouble(dr["NetSheet_MiscellaneousClosingCosts_WithLoan"]);

            double dblHoaEstimated = Convert.ToDouble(dr["NetSheet_HOADues_Estimated"]);
            double dblHoaActual = Convert.ToDouble(dr["NetSheet_HOADues_Actual"]);
            double dblHoaLoan = Convert.ToDouble(dr["NetSheet_HOADues_WithLoan"]);

            double dblTransactionEstimated = Convert.ToDouble(dr["NetSheet_TransactionFee_Estimated"]);
            double dblTransactionActual = Convert.ToDouble(dr["NetSheet_TransactionFee_Actual"]);
            double dblTransactionLoan = Convert.ToDouble(dr["NetSheet_TransactionFee_WithLoan"]);

            double dblOtherEstimated = Convert.ToDouble(dr["NetSheet_Other_Estimated"]);
            double dblOtherActual = Convert.ToDouble(dr["NetSheet_Other_Actual"]);
            double dblOtherLoan = Convert.ToDouble(dr["NetSheet_Other_WithLoan"]);

            double dblTotalClosingCostsEstimated = Convert.ToDouble(dr["NetSheet_Total_ClosingCost_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_ClosingCost_Estimated"]))
                dblTotalClosingCostsEstimated = dblBrokerEstimated + dblSellerClosingEstimated + dblEscrowEstimated +
                                                dblSellersCostEstimated + dblHomeEstimated + dblAppraisalEstimated +
                                                dblMiscClosingEstimated + dblHoaEstimated + dblTransactionEstimated + dblOtherEstimated;
            tbTotalClosingCostsEstimated.InnerText = dblTotalClosingCostsEstimated.ToString("c");
            double dblTotalClosingCostsActual = Convert.ToDouble(dr["NetSheet_Total_ClosingCost_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_ClosingCost_Actual"]))
                dblTotalClosingCostsActual = dblBrokerActual + dblSellerClosingActual + dblEscrowActual +
                                                dblSellersCostActual + dblHomeActual + dblAppraisalActual +
                                                dblMiscClosingActual + dblHoaActual + dblTransactionActual + dblOtherActual;
            tbTotalClosingCostsActual.InnerText = dblTotalClosingCostsActual.ToString("c");
            double dblTotalClosingCostsLoan = Convert.ToDouble(dr["NetSheet_Total_ClosingCost_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_ClosingCost_WithLoan"]))
                dblTotalClosingCostsLoan = dblBrokerLoan + dblSellerClosingLoan + dblEscrowLoan +
                                                dblSellersCostLoan + dblHomeLoan + dblAppraisalLoan +
                                                dblMiscClosingLoan + dblHoaLoan + dblTransactionLoan + dblOtherLoan;
            tbTotalClosingCostsLoan.InnerText = dblTotalClosingCostsLoan.ToString("c");


            #endregion

            #region LoanCost

            double dblPurchasePlusRehab = Convert.ToDouble(dr["NetSheet_Note"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Note"]))
                dblPurchasePlusRehab = dblAcquisitionEstimated + dblRehabCostEstimated;
            tbPurchasePlusRehab.InnerText = dblPurchasePlusRehab.ToString("c");
            
            double dbl1stDOT = Convert.ToDouble(dr["NetSheet_1stDOT"]);
            double dbl2ndDOT = Convert.ToDouble(dr["NetSheet_2ndDOT"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_2ndDOT"]))
                dbl2ndDOT = dblPurchasePlusRehab - dbl1stDOT;
            tb2ndDOT.InnerText = dbl2ndDOT.ToString("c");

            double dblLoanFeePercent = Convert.ToDouble(dr["NetSheet_LoanFee"]);
            double dblLoanFeeCalculated = Convert.ToDouble(dr["NetSheet_LoanFeeSumma"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_LoanFee"]))
                dblLoanFeeCalculated = dbl1stDOT * dblLoanFeePercent / 100;
            tbLoanFeeCalculated.InnerText = dblLoanFeeCalculated.ToString("c");
            
            double dblLoanInterestPercent = Convert.ToDouble(dr["NetSheet_LoanInterest"]);
            double dblLoanInterestCalculated = Convert.ToDouble(dr["NetSheet_LoanInterestSumma"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_LoanInterest"]))
                dblLoanInterestCalculated = dbl1stDOT * dblLoanInterestPercent / 100;
            tbLoanInterestCalculated.InnerText = dblLoanInterestCalculated.ToString("c");
            
            double dblLoanBrokerFee = Convert.ToDouble(dr["NetSheet_LoanBrokerFee"]);
            double dblLoanTotal = Convert.ToDouble(dr["NetSheet_Total_LoanCost"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_LoanCost"]))
                dblLoanTotal = dblLoanBrokerFee + dblLoanFeeCalculated + dblLoanInterestCalculated;
            tbLoanTotal.InnerText = dblLoanTotal.ToString("c");
            #endregion

            #region NetProfit
            
            double dblNetProfitEstimated = Convert.ToDouble(dr["NetSheet_NetProfit_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NetProfit_Estimated"]))
                dblNetProfitEstimated = dblSellingPriceEstimated - dblTotalPresaleEstimated - dblTotalClosingCostsEstimated;
            tbNetProfitEstimated.InnerText = dblNetProfitEstimated.ToString("c");
            double dblNetProfitActual = Convert.ToDouble(dr["NetSheet_NetProfit_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NetProfit_Actual"]))
                dblNetProfitActual = dblSellingPriceActual - dblTotalPresaleActual - dblTotalClosingCostsActual;
            tbNetProfitActual.InnerText = dblNetProfitActual.ToString("c");
            double dblNetProfitLoan = Convert.ToDouble(dr["NetSheet_NetProfit_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NetProfit_WithLoan"]))
                dblNetProfitLoan = dblSellingPriceLoan - dblTotalPresaleLoan - dblTotalClosingCostsLoan - dblLoanTotal;
            tbNetProfitLoan.InnerText = dblNetProfitLoan.ToString("c");

            double dblProfitParticipationPercent = Convert.ToDouble(dr["NetSheet_FeeLCH"]);
            double dblProfitParticipationEstimated = Convert.ToDouble(dr["NetSheet_ProfitParticipation_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ProfitParticipation_Estimated"]))
                dblProfitParticipationEstimated = dblNetProfitEstimated * dblProfitParticipationPercent / 100;
            tbProfitParticipationEstimated.InnerText = dblProfitParticipationEstimated.ToString("c");
            double dblProfitParticipationActual = Convert.ToDouble(dr["NetSheet_ProfitParticipation_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ProfitParticipation_Actual"]))
                dblProfitParticipationActual = dblNetProfitActual * dblProfitParticipationPercent / 100;
            tbProfitParticipationActual.InnerText = dblProfitParticipationActual.ToString("c");
            double dblProfitParticipationLoan = Convert.ToDouble(dr["NetSheet_ProfitParticipation_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ProfitParticipation_WithLoan"]))
                dblProfitParticipationLoan = dblNetProfitLoan * dblProfitParticipationPercent / 100;
            tbProfitParticipationLoan.InnerText = dblProfitParticipationLoan.ToString("c");

            double dblEquityEstimated = Convert.ToDouble(dr["NetSheet_Total_Equity_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_Equity_Estimated"]))
                dblEquityEstimated = dblTotalPresaleEstimated;
            tbEquityEstimated.InnerText = dblEquityEstimated.ToString("c");
            double dblEquityActual = Convert.ToDouble(dr["NetSheet_Total_Equity_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_Equity_Actual"]))
                dblEquityActual = dblTotalPresaleActual;
            tbEquityActual.InnerText = dblEquityActual.ToString("c");
            double dblEquityLoan = Convert.ToDouble(dr["NetSheet_Total_Equity_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_Equity_WithLoan"]))
                dblEquityLoan = dbl2ndDOT + dblLoanTotal;
            tbEquityLoan.InnerText = dblEquityLoan.ToString("c");

            double dblTotalNetProfitEstimated = Convert.ToDouble(dr["NetSheet_Total_NetProfit_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetProfit_Estimated"]))
                dblTotalNetProfitEstimated = Math.Round(dblNetProfitEstimated - dblProfitParticipationEstimated, 0);
            tbTotalNetProfitEstimated.InnerText = dblTotalNetProfitEstimated.ToString("c");
            double dblTotalNetProfitActual = Convert.ToDouble(dr["NetSheet_Total_NetProfit_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetProfit_Actual"]))
                dblTotalNetProfitActual = Math.Round(dblNetProfitActual - dblProfitParticipationActual, 0);
            tbTotalNetProfitActual.InnerText = dblTotalNetProfitActual.ToString("c");
            double dblTotalNetProfitLoan = Convert.ToDouble(dr["NetSheet_Total_NetProfit_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetProfit_WithLoan"]))
                dblTotalNetProfitLoan = Math.Round(dblNetProfitLoan - dblProfitParticipationLoan, 0);
            tbTotalNetProfitLoan.InnerText = dblTotalNetProfitLoan.ToString("c");

            double dblTotalNetPercentEstimated = Convert.ToDouble(dr["NetSheet_Total_NetPercent_Estimated"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetPercent_Estimated"]))
                dblTotalNetPercentEstimated = dblTotalNetProfitEstimated / (dblEquityEstimated == 0 ? 1 : dblEquityEstimated) * 100;
            tbTotalNetPercentEstimated.InnerText = dblTotalNetPercentEstimated.ToString("#0.00") + "%";
            double dblTotalNetPercentActual = Convert.ToDouble(dr["NetSheet_Total_NetPercent_Actual"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetPercent_Actual"]))
                dblTotalNetPercentActual = dblTotalNetProfitActual / (dblEquityActual == 0 ? 1 : dblEquityActual) * 100;
            tbTotalNetPercentActual.InnerText = dblTotalNetPercentActual.ToString("#0.00") + "%";
            double dblTotalNetPercentLoan = Convert.ToDouble(dr["NetSheet_Total_NetPercent_WithLoan"]);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetPercent_WithLoan"]))
                dblTotalNetPercentLoan = dblTotalNetProfitLoan / (dblEquityLoan == 0 ? 1 : dblEquityLoan) * 100;
            tbTotalNetPercentLoan.InnerText = dblTotalNetPercentLoan.ToString("#0.00") + "%";
            
            #endregion
        }

        int GetEstimatedValue()
        {
            int? purchasePrice = PurchasePrice();
            return purchasePrice.HasValue ? purchasePrice.Value : BuyerPurchasePrice().Value;
        }

        int? BuyerPurchasePrice()
        {
            DataAccess da = new DataAccess();
            DataTable tbl =
            da.ExecuteSQL("select BuyerPurchasePrice from LvReo_MasterList where PropertyId=" + GetPropertyId());
            if (!tbl.Rows[0].IsNull(0))
                return Utilities.StrToInt(tbl.Rows[0][0].ToString());
            else
                return 0;
        }

        int? PurchasePrice()
        {
            DataAccess da = new DataAccess();
            DataTable tbl =
            da.ExecuteSQL("select PurchasePrice from LvReo_MasterList where PropertyId=" + GetPropertyId());
            if (!tbl.Rows[0].IsNull(0))
                return Utilities.StrToInt(tbl.Rows[0][0].ToString());
            else
                return 0;
        }

        /*int? GetRehabCostsValue()
        {
            DataAccess da = new DataAccess();
            DataTable tbl =
                  da.ExecuteSQL("select RehabCosts from LvReo_MasterList where PropertyId=" + GetPropertyId());

            var str = tbl.Rows[0][0].ToString();
            str = str.Replace(',', '.').Split('.')[0];
            if (string.IsNullOrEmpty(str)) return 0;
            return Convert.ToInt32(str);
        }*/

        int GetPropertyId()
        {
          var str = Request.QueryString["PropertyId"].ToString();
          return Convert.ToInt32(str);
        }

        bool IsPrintView()
        {
            bool result = false;
            if (Request.QueryString["PrintView"] != null)
                if (Convert.ToInt32(Request.QueryString["PrintView"]) == 1)
                    result = true;
            return result;
        }

        bool IsLoan()
        {
            bool result = false;
            if (Request.QueryString["IsLoan"] != null)
                if (Convert.ToInt32(Request.QueryString["IsLoan"]) == 1)
                    result = true;
            return result;
        }

        protected void RefreshLoan(bool isLoan)
        {
            tbodyLoan.Visible = isLoan;
            tbPurchasePlusRehab.Visible = isLoan;
            tb1stDOT.Visible = isLoan;
            lblPurchasePlusRehab.Visible = isLoan;
            lbl1stDOT.Visible = isLoan;
        }        
    }
}
